package lumis.service.image;

import lumis.portal.PortalException;
import lumis.portal.UnexpectedException;
import lumis.portal.dao.jdbc.ITransactionJdbc;
import lumis.util.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;

/**
 * Dao implementation for images from the Image Service, based on JDBC.
 *
 * @version $Revision: 8790 $ $Date: 2008-01-21 16:05:30 -0200 (Mon, 21 Jan 2008) $
 * @since 4.0.0
 */
class ImageDaoJdbc implements IImageDao
{
	public ImageConfig get(String imageId, ITransaction transaction) throws PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			PreparedStatement statement = connection.prepareStatement("select id, type, imageFile, parentFolder, title, legend, credits from lum_Image where id = ?");
			try
			{
				statement.setString(1, imageId);
				ResultSet resultSet = statement.executeQuery();
				try
				{
					if (resultSet.next())
					{
						ImageConfig imageConfig = new ImageConfig();
						imageConfig.setId(resultSet.getString("id"));
						imageConfig.setType(resultSet.getInt("type"));
						imageConfig.setImageFile(resultSet.getString("imageFile"));
						imageConfig.setParentFolder(resultSet.getString("parentFolder"));
						imageConfig.setTitle(resultSet.getString("title"));
						imageConfig.setLegend(resultSet.getString("legend"));
						imageConfig.setCredits(resultSet.getString("credits"));
						return imageConfig;
					}
					else
						return null;
					
				}
				finally
				{
					resultSet.close();				
				}				
			}
			finally
			{
				statement.close();				
			}
		}
		catch (SQLException e)
		{
			throw new UnexpectedException(e);
		}
	}

	public void update(ImageConfig imageConfig, ITransaction transaction) throws PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			PreparedStatement statement = connection.prepareStatement("update lum_Image set type=?, imageFile=?, parentFolder=?, title=?, legend=?, credits=? where id = ?");
			try
			{
				statement.setInt(1, imageConfig.getType());
				statement.setString(2, imageConfig.getImageFile());
				statement.setString(3, imageConfig.getParentFolder());
				statement.setString(4, imageConfig.getTitle());
				statement.setString(5, imageConfig.getLegend());
				statement.setString(6, imageConfig.getCredits());
				statement.setString(7, imageConfig.getId());
				statement.executeUpdate();
			}
			finally
			{
				statement.close();
			}
		}
		catch (SQLException e)
		{
			throw new UnexpectedException(e);
		}		
	}
	
	public boolean delete(String imageId, ITransaction transaction) throws PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			PreparedStatement statement = connection.prepareStatement("delete from lum_Image where id = ?");
			try
			{
				statement.setString(1, imageId);
				return statement.executeUpdate() > 0;				
			}
			finally
			{
				statement.close();				
			}
		}
		catch (SQLException e)
		{
			throw new UnexpectedException(e);
		}
	}
	
	
	public Collection<String> getIdsByParentFolderAndType(String serviceInstanceId, String folderId, Integer type, ITransaction transaction) throws PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			StringBuilder sql = new StringBuilder(200);
			sql.append("select lum_Image.id from lum_Image INNER JOIN " +
					"lum_ContentVersion ON lum_Image.id = lum_ContentVersion.itemId INNER JOIN " +
					"lum_ContentLocale ON lum_ContentVersion.contentLocaleId = lum_ContentLocale.id INNER JOIN " +
					"lum_Content ON lum_ContentLocale.contentId = lum_Content.id " +
					"where lum_Content.serviceInstanceId = ? and ");
			if (folderId != null)				
				sql.append("lum_Image.parentFolder = ?");
			else
				sql.append("lum_Image.parentFolder IS NULL");
			if (type != null)
				sql.append(" and lum_Image.type = " + type);
			PreparedStatement statement = connection.prepareStatement(sql.toString());
			try
			{
				statement.setString(1, serviceInstanceId);
				if (folderId != null)					
					statement.setString(2, folderId);
				Collection<String> returnValues = JdbcUtil.fillListString(statement, "id");
				return returnValues;
			}
			finally
			{
				statement.close();
			}
		}
		catch (SQLException e)
		{
			throw new UnexpectedException(e);
		}		
	}
	
	public Collection<String> getFolderIdsByParentAndTitle(String serviceInstanceId, String parentFolderId, String folderName, ITransaction transaction) throws PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();
			
			String sql = "SELECT lum_Image.id FROM lum_Image INNER JOIN " +
					"lum_ContentVersion ON lum_Image.id = lum_ContentVersion.itemId INNER JOIN " +
					"lum_ContentLocale ON lum_ContentVersion.contentLocaleId = lum_ContentLocale.id INNER JOIN " +
					"lum_Content ON lum_ContentLocale.contentId = lum_Content.id " +
					"WHERE lum_Image.type = " + ImageConfig.TYPE_FOLDER + 
					" AND lum_Image.title = ? AND lum_Content.serviceInstanceId = ? AND lum_Image.parentFolder ";
			if (parentFolderId != null)
				sql += "= ?";
			else
				sql += "IS NULL";
			PreparedStatement statement = connection.prepareStatement(sql);
			try
			{
				statement.setString(1, folderName);
				statement.setString(2, serviceInstanceId);
				if (parentFolderId != null)					
					statement.setString(3, parentFolderId);
				return JdbcUtil.fillListString(statement, "id");
			}
			finally
			{
				statement.close();
			}
		}
		catch (SQLException e)
		{
			throw new UnexpectedException(e);
		}				
	}
}
